package main

import (
	"RDSSQLServer/common"
	"database/sql"
	"fmt"
	"github.com/pkg/errors"
	"log"
)

type animal struct {
	id   int
	name string
	age  int
}

func checkError(err error) {
	if err != nil {
		log.Printf("%+v", err)
	}
}

func main() {
	db := common.GetDb()
	defer db.Close()
	err := createTable(db)
	if err != nil {
		log.Printf("%+v", err)
		return
	}
	//插入数据
	err = insert(db)
	if err != nil {
		log.Printf("%+v", err)
		return
	}
	//更新数据
	err = update(db)
	if err != nil {
		log.Printf("%+v", err)
		return
	}
	//删除数据
	err = delete(db)
	if err != nil {
		log.Printf("%+v", err)
		return
	}
	//查询一条数据
	animals, err := query(db, 2)
	if err != nil {
		log.Printf("%+v", err)
		return
	}
	fmt.Println(animals.name, animals.age)
	//查询多条数据
	count, err := queryall(db)
	if err != nil {
		log.Printf("%+v", err)
		return
	}
	fmt.Println(count)
}

func createTable(db *sql.DB) error {
	_, err := db.Exec("CREATE TABLE animals (id INT IDENTITY(1,1) NOT NULL PRIMARY KEY,name VARCHAR(20) DEFAULT NULL,age NUMERIC(2) DEFAULT 0,);")
	if err != nil {
		return errors.Wrap(err, "ERR: fail to createTable")
	}
	return nil
}

//插入数据
func insert(db *sql.DB) error {
	var ID int64
	ID = 9
	smt, err := db.Prepare("INSERT INTO animals (name,age) VALUES (@name,@age) ;select isNull(SCOPE_IDENTITY(), -1);")
	if err != nil {
		return errors.Wrap(err, "ERR: fail to prepare")
	}
	defer smt.Close()
	err = smt.QueryRow(sql.Named("name", "狮子"), sql.Named("age", 12)).Scan(&ID)
	if err != nil {
		return errors.Wrap(err, "ERR: fail to insert")
	}
	fmt.Println("插入数据的id是", ID)
	return nil
}

//查询一条数据
func query(db *sql.DB, id int) (animal, error) {
	animals := animal{}
	row := db.QueryRow("select name,age from animals where id=@id", sql.Named("id", id))
	if row != nil {
		err := row.Scan(&animals.name, &animals.age)
		if err != nil {
			return animals, errors.Wrap(err, "ERR: fail to query")
		}
		return animals, nil
	}
	return animals, nil
}

//查询多条数据
func queryall(db *sql.DB) (int, error) {
	tsq := fmt.Sprintf("select id,name,age from animals;")
	rows, err := db.Query(tsq)
	if err != nil {
		return -1, errors.Wrap(err, "ERR: fail to query")
	}
	defer rows.Close()
	var count int
	for rows.Next() {
		an := animal{}
		err := rows.Scan(&an.id, &an.name, &an.age)
		if err != nil {
			return -1, errors.Wrap(err, "ERR: fail to query")
		}
		fmt.Println(an.id, an.name, an.age)
		count++
	}
	return count, nil
}

//更新数据
func update(db *sql.DB) error {
	_, err := db.Exec("update animals set name=@name,age=@age where id=@id", sql.Named("name", "兔子"), sql.Named("age", 7), sql.Named("id", 1))
	if err != nil {
		return errors.Wrap(err, "ERR: fail to update")
	}
	return nil
}

//删除数据
func delete(db *sql.DB) error {
	_, err := db.Exec("delete from animals  where id=@id", sql.Named("id", 1))
	if err != nil {
		return errors.Wrap(err, "ERR: fail to delete")
	}
	return nil
}
